#!/usr/bin/env node

// 改进的参考编号回填脚本
// 使用护照号码前四位+后一位进行精确匹配
// Usage (Windows PowerShell):
//   node app/tools/improved-backfill-reference.mjs --commit
//   node app/tools/improved-backfill-reference.mjs --dry-run

import fs from 'node:fs'
import fsp from 'node:fs/promises'
import path from 'node:path'
import process from 'node:process'
import { fileURLToPath } from 'node:url'
import { PrismaClient } from '@prisma/client'
import { load as loadHtml } from 'cheerio'

const __filename = fileURLToPath(import.meta.url)
const __dirname = path.dirname(__filename)

const prisma = new PrismaClient()

/** 清理参考编号，移除空格 */
function normalizeReferenceNumber(raw) {
  if (!raw)
    return ''
  return String(raw).replace(/\s+/g, '').trim()
}

/** 从文本中提取第一个邮箱 */
function extractEmailFromText(text) {
  if (!text)
    return ''
  const re = /[\w.%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/i
  const m = text.match(re)
  return m ? m[0] : ''
}

/** 提取护照号码的前四位+后一位用于匹配（允许任意字母数字作为末尾） */
function extractPassportPattern(passportText) {
  if (!passportText)
    return ''

  // 兼容：K260****9 / G458****1 / AB12****Z 等
  const match = passportText.match(/([A-Z0-9]{4})\*+([A-Z0-9])/i)
  if (match) {
    return {
      prefix: match[1], // 前四位
      suffix: match[2], // 末尾一位
      pattern: `${match[1]}****${match[2]}`,
    }
  }
  return null
}

/** 检查数据库中的护照号码是否匹配HTML中的脱敏格式 */
function matchesPassportPattern(fullPassport, pattern) {
  if (!fullPassport || !pattern)
    return false

  const { prefix, suffix } = pattern
  if (!prefix || !suffix)
    return false

  // 检查前四位和后一位是否匹配
  return fullPassport.startsWith(prefix) && fullPassport.endsWith(suffix)
}

/**
 * 解析HTML提取所需数据
 */
function parseHtmlForData(html) {
  try {
    const $ = loadHtml(html)

    // 提取参考编号
    let referenceRaw = ''
    $('td.content4').each((_i, el) => {
      const label = $(el).text().trim()
      if (/參考編號|参考编号/.test(label)) {
        const valueTd = $(el).next('td')
        const bold = valueTd.find('b').first()
        const text = (bold.text() || valueTd.text() || '').trim()
        if (text) {
          referenceRaw = text
        }
      }
    })

    // 提取护照号码脱敏信息
    let passportPattern = null
    $('td.content4').each((_i, el) => {
      const label = $(el).text().trim()
      if (/護照號碼|护照号码|香港身分證號碼/.test(label)) {
        const valueTd = $(el).next('td')
        const passportText = valueTd.text().trim()
        passportPattern = extractPassportPattern(passportText)
      }
    })

    // 提取邮箱（优先使用隐藏input）
    const hiddenEmail = (
      $('input[name="storedAppointmentConfirmationEmail"]').attr('value')
      || $('input[name="storedAppointmentReminderEmail"]').attr('value')
      || ''
    ).trim()

    const bodyText = $('body').text() || ''
    const email = hiddenEmail || extractEmailFromText(bodyText)

    // 提取其他预约信息
    const date = ($('input[name="storedAppointmentTimeslotDate"]').attr('value') || '').trim()
    const fromRaw = ($('input[name="storedAppointmentTimeslotFromTime"]').attr('value') || '').trim()
    const toRaw = ($('input[name="storedAppointmentTimeslotToTime"]').attr('value') || '').trim()
    const officeCode = ($('input[name="storedAppointmentOfficeCode"]').attr('value') || '').trim()
    const timeslotId = ($('input[name="storedAppointmentTimeslotId"]').attr('value') || '').trim()

    function formatTime(t) {
      if (!t)
        return ''
      const digits = String(t).replace(/\D/g, '')
      const s = digits.padStart(4, '0')
      return `${s.slice(0, 2)}:${s.slice(2)}`
    }
    const apptTime = fromRaw && toRaw ? `${formatTime(fromRaw)}-${formatTime(toRaw)}` : ''

    const referenceNumber = normalizeReferenceNumber(referenceRaw)

    return {
      referenceNumber,
      email,
      passportPattern,
      date,
      time: apptTime,
      officeCode,
      timeslotId,
    }
  }
  catch (error) {
    console.error('[ERROR] 解析HTML失败:', error.message)
    return {
      referenceNumber: '',
      email: '',
      passportPattern: null,
      date: '',
      time: '',
      officeCode: '',
      timeslotId: '',
    }
  }
}

/** 从快照文件路径提取时间戳 */
function parseSnapshotTimestampFromPath(filePath) {
  const base = path.basename(filePath)
  const m = base.match(/(\d{4}-\d{2}-\d{2})_(\d{2}-\d{2}-\d{2})/)
  if (!m)
    return null

  const [datePart, timePart] = [m[1], m[2]]
  const iso = `${datePart}T${timePart.replace(/-/g, ':')}.000Z`
  const dt = new Date(iso)
  return Number.isNaN(dt.getTime()) ? null : dt
}

/** 递归扫描HTML文件 */
async function walkHtmlFiles(dir) {
  const results = []
  const stack = [dir]

  while (stack.length > 0) {
    const current = stack.pop()
    let entries
    try {
      entries = await fsp.readdir(current, { withFileTypes: true })
    }
    catch {
      continue
    }

    for (const ent of entries) {
      const p = path.join(current, ent.name)
      if (ent.isDirectory()) {
        stack.push(p)
      }
      else if (ent.isFile() && p.toLowerCase().endsWith('.html')) {
        // 只处理final相关的HTML文件
        if (ent.name.includes('final')) {
          results.push(p)
        }
      }
    }
  }
  return results
}

/** 查找匹配的数据库记录（多策略） */
async function findMatchingRecord({ email, passportPattern, date, time, officeCode, timeslotId, snapDt }) {
  // 公共条件：只考虑缺失参考号的候选
  const missingRef = { OR: [{ reference_number: null }, { reference_number: '' }] }

  // 查询策略按优先级依次尝试，命中即返回

  // S1) 邮箱 + 可选护照掩码 + 可选日期/时间/办事处
  if (email) {
    try {
      const emailMatches = await prisma.appointment_management.findMany({
        where: { email, ...missingRef },
        orderBy: { updated_at: 'desc' },
        take: 50,
      })
      let filtered = emailMatches
      if (passportPattern) {
        filtered = filtered.filter(rec => matchesPassportPattern(rec.passport_number, passportPattern))
      }
      if (date && time) {
        filtered = filtered.filter(rec => rec.appointment_date === date && rec.appointment_time === time)
      }
      if (officeCode) {
        filtered = filtered.filter(rec => !rec.office_code || rec.office_code === officeCode)
      }
      if (filtered.length > 0)
        return filtered[0]
    }
    catch (error) {
      console.warn('[WARN] S1 邮箱查询失败:', error.message)
    }
  }

  // S2) timeslotId 精确匹配
  if (timeslotId) {
    try {
      const rows = await prisma.appointment_management.findMany({
        where: { timeslot_id: timeslotId, ...missingRef },
        orderBy: { updated_at: 'desc' },
        take: 10,
      })
      if (rows.length > 0)
        return rows[0]
    }
    catch (error) {
      console.warn('[WARN] S2 时段ID查询失败:', error.message)
    }
  }

  // S3) 日期+时间(+办事处) 精确匹配
  if (date && time) {
    try {
      const where = { appointment_date: date, appointment_time: time, ...missingRef }
      const rows = await prisma.appointment_management.findMany({
        where: officeCode ? { ...where, office_code: officeCode } : where,
        orderBy: { updated_at: 'desc' },
        take: 50,
      })
      if (rows.length > 0)
        return rows[0]
    }
    catch (error) {
      console.warn('[WARN] S3 日期时间查询失败:', error.message)
    }
  }

  // S4) 护照掩码（startsWith + endsWith）
  if (passportPattern && passportPattern.prefix && passportPattern.suffix) {
    try {
      const rows = await prisma.appointment_management.findMany({
        where: {
          ...missingRef,
          AND: [
            { passport_number: { startsWith: passportPattern.prefix } },
            { passport_number: { endsWith: passportPattern.suffix } },
          ],
        },
        orderBy: { updated_at: 'desc' },
        take: 50,
      })
      if (rows.length > 0)
        return rows[0]
    }
    catch (error) {
      console.warn('[WARN] S4 护照掩码查询失败:', error.message)
    }
  }

  // S5) 兜底：按快照时间临近的缺失参考号记录（最近优先）
  if (snapDt instanceof Date && !Number.isNaN(snapDt.getTime())) {
    try {
      const start = new Date(snapDt.getTime() - 72 * 3600 * 1000)
      const end = new Date(snapDt.getTime() + 24 * 3600 * 1000)
      const rows = await prisma.appointment_management.findMany({
        where: {
          ...missingRef,
          created_at: { gte: start, lte: end },
        },
        orderBy: { created_at: 'desc' },
        take: 50,
      })
      if (rows.length > 0)
        return rows[0]
    }
    catch (error) {
      console.warn('[WARN] S5 快照时间窗口查询失败:', error.message)
    }
  }

  // S6) 最后兜底：全表缺失参考号（最新）
  try {
    const rows = await prisma.appointment_management.findMany({
      where: { ...missingRef },
      orderBy: { updated_at: 'desc' },
      take: 1,
    })
    return rows[0] || null
  }
  catch (error) {
    console.warn('[WARN] S6 兜底查询失败:', error.message)
  }

  return null
}

async function main() {
  const argv = process.argv.slice(2)
  const args = new Set(argv)
  const commit = args.has('--commit') && !args.has('--dry-run')

  // 解析 --dir 参数（可多次传入或逗号分隔），支持绝对/相对路径
  const cliDirs = []
  for (let i = 0; i < argv.length; i++) {
    if (argv[i] === '--dir') {
      const val = argv[i + 1]
      if (!val) {
        console.error('[ERROR] --dir 需要提供路径')
        process.exit(2)
      }
      const parts = String(val).split(',').map(s => s.trim()).filter(Boolean)
      for (const p of parts) {
        const resolved = path.isAbsolute(p) ? p : path.resolve(process.cwd(), p)
        cliDirs.push(resolved)
      }
      i++
    }
  }

  // 自动检测默认目录：automation-run-snapshots-1 与 automation-run-snapshots
  const defaultCandidates = [
    path.resolve(__dirname, '..', 'automation-run-snapshots-1'),
    path.resolve(__dirname, '..', 'automation-run-snapshots'),
  ]
  const baseDirs = (cliDirs.length > 0 ? cliDirs : defaultCandidates).filter(d => fs.existsSync(d))

  if (baseDirs.length === 0) {
    console.error('[ERROR] 未找到可用的快照目录。可通过 --dir 指定目录，或确保默认目录存在: app/automation-run-snapshots-1, app/automation-run-snapshots')
    process.exit(2)
  }

  console.log(`[INFO] 扫描HTML目录: ${baseDirs.join(', ')}`)
  const filesSet = new Set()
  for (const dir of baseDirs) {
    try {
      const files = await walkHtmlFiles(dir)
      console.log(`  - ${dir}: ${files.length} 个final相关HTML`)
      for (const f of files) filesSet.add(f)
    }
    catch (e) {
      console.warn(`[WARN] 扫描目录失败: ${dir} -> ${e?.message || e}`)
    }
  }
  const files = Array.from(filesSet)
  console.log(`[INFO] 合并后共 ${files.length} 个HTML文件待处理`)

  let parsedCount = 0
  let matchedCount = 0
  let updatedCount = 0
  let skippedNoData = 0
  let skippedNoMatch = 0
  let uniqueConflict = 0
  let passportMismatch = 0

  for (const file of files) {
    let html
    try {
      html = await fsp.readFile(file, 'utf8')
    }
    catch {
      continue
    }

    const { referenceNumber, email, passportPattern, date, time, officeCode, timeslotId } = parseHtmlForData(html)
    const snapDt = parseSnapshotTimestampFromPath(file)
    parsedCount++

    console.log(`\n[INFO] 处理文件: ${path.basename(file)}`)
    console.log(`  参考编号: ${referenceNumber}`)
    console.log(`  邮箱: ${email}`)
    console.log(`  护照模式: ${passportPattern ? passportPattern.pattern : '无'}`)
    console.log(`  预约信息: ${date} ${time}`)

    if (!referenceNumber || !email) {
      skippedNoData++
      console.log(`  [SKIP] 缺少参考编号或邮箱`)
      continue
    }

    matchedCount++

    // 查找匹配的数据库记录（多策略）
    const target = await findMatchingRecord({ email, passportPattern, date, time, officeCode, timeslotId, snapDt })

    if (!target) {
      skippedNoMatch++
      console.log(`  [SKIP] 未找到匹配的数据库记录`)
      continue
    }

    // 验证护照号码匹配（如果有护照模式）
    if (passportPattern && !matchesPassportPattern(target.passport_number, passportPattern)) {
      passportMismatch++
      console.log(`  [SKIP] 护照号码不匹配: ${target.passport_number} vs ${passportPattern.pattern}`)
      continue
    }

    console.log(`  [MATCH] 找到匹配记录 ID: ${target.id}, 护照: ${target.passport_number}`)

    if (commit) {
      try {
        // 仅补全缺失字段，避免覆盖已有有效数据
        const data = { reference_number: referenceNumber }
        if ((!target.appointment_date || target.appointment_date === '') && date) {
          data.appointment_date = date
        }
        if ((!target.appointment_time || target.appointment_time === '') && time) {
          data.appointment_time = time
        }
        if ((!target.office_code || target.office_code === '') && officeCode) {
          data.office_code = officeCode
        }
        // 若状态未置为 booked，则设置并补齐 booked_at
        if (target.appointment_status !== 'booked') {
          data.appointment_status = 'booked'
          data.booked_at = snapDt instanceof Date && !Number.isNaN(snapDt.getTime()) ? snapDt : new Date()
        }

        await prisma.appointment_management.update({ where: { id: target.id }, data })
        updatedCount++
        console.log(`  [SUCCESS] 更新成功: ${email} -> ${referenceNumber}`)
      }
      catch (e) {
        const code = e?.code || ''
        if (code === 'P2002') {
          uniqueConflict++
          console.warn(`  [SKIP] 参考编号重复: ${referenceNumber}`)
        }
        else {
          console.warn(`  [ERROR] 更新失败: ${e?.message || e}`)
        }
      }
    }
    else {
      console.log(`  [DRYRUN] 将更新: ${email} -> ${referenceNumber}`)
    }
  }

  console.log('\n=== 汇总统计 ===')
  console.log(`扫描文件数: ${files.length}`)
  console.log(`解析页面数: ${parsedCount}`)
  console.log(`有效匹配数: ${matchedCount}`)
  console.log(`成功更新数: ${updatedCount}`)
  console.log(`跳过(无数据): ${skippedNoData}`)
  console.log(`跳过(无匹配): ${skippedNoMatch}`)
  console.log(`护照不匹配: ${passportMismatch}`)
  if (uniqueConflict > 0) {
    console.log(`唯一约束冲突: ${uniqueConflict}`)
  }
}

main()
  .catch((e) => {
    console.error('[ERROR]', e)
    process.exit(1)
  })
  .finally(async () => {
    try {
      await prisma.$disconnect()
    }
    catch {}
  })
